package com.renbang.dao.sqlsession;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import com.renbang.model.common.PageData;
/**
 * 
 * @description 数据库工具类
 * @author user
 * @date 2017年5月3日
 */
public class DBUtils {
		private static Connection con;
		public static String DRIVER; 
		private static String USER;
		private static String PASS;
		private static String URL;
		
		/*public static String DRIVER ="com.microsoft.sqlserver.jdbc.SQLServerDriver";
		private static String USER="sa";
		private static String PASS="1234";
		private static String URL="jdbc:sqlserver://127.0.0.1:1433;databaseName=renbanghome;";*/
		
		//在Action层通过注解拿到连接属性传过来设置连接
		public static void setConnection(String DRIVER,String URL,String USER,String PASS){
			DBUtils.DRIVER=DRIVER;
			DBUtils.URL=URL;
			DBUtils.USER=USER;
			DBUtils.PASS=PASS;
			
		}
		
		public static 	Connection  getConnection() throws Exception{
			if(con==null){
				Class.forName(DRIVER);
				con=DriverManager.getConnection(URL, USER, PASS);	
				}
			return con;
		}
		/**
		 * 
		 * @Description:传入表名和字段名和where条件得到数据
		 * @author:user
		 * @param tabName 表名
		 * @param colums 字段名
		 * @param where 条件
		 * @return List
		 * @date 2017年5月3日
		 */
		public static List getList(String tabName,String  colums,String where) throws Exception{
			String sql = "select "+colums+" from "+tabName+" where 1=1 "+where;
			/* select top ${page.limit-page.offset } * from (SELECT ROW_NUMBER() 
						OVER (ORDER BY id) AS Rownum,* from Cw_link where 1=1
						<include refid="where" />
						)a
						where Rownum > #{page.offset }*/
			Connection con=getConnection();
			Statement stmt = con.createStatement();
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			List<Map> list = new ArrayList<Map>();
			String[]colarr = new String[1];
			Map obj ;
			//如果是查询多个字段,把字段名分割放进数组
			if(colums.contains(",")){
				colarr = colums.split(",");
			}else{
				//只查一个字段
				colarr[0]=colums;
			}
			while(rs.next()){
				obj = new PageData();
				//便利这个字段名分割的数组
				for (String col : colarr) {
					obj.put(col.trim(), rs.getString(col.trim()));
				}
				list.add(obj);
			}
			return list;
		}
		/**
		 * 
		 * @Description:分页查询数据
		 * @author:user
		 * @param tabName 表名
		 * @param colums 字段名
		 * @param where 条件
		 * @param offset,limit 分页条件
		 * @return List
		 * @date 2017年5月3日
		 */
		public static List getList(String tabName,String  colums,String where,Integer offset,Integer limit) throws Exception{
			/*String sql = "select "+colums+" from "+tabName+" where 1=1 "+where;*/
			String sql = 	" select top " + (limit-offset) +" * from (SELECT ROW_NUMBER()"+ 
						"OVER (ORDER BY id) AS Rownum,* from "+ tabName +" where 1=1 "+
						where+
						")a where Rownum >"+ offset;
			Connection con=getConnection();
			Statement stmt = con.createStatement();
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			List<Map> list = new ArrayList<Map>();
			String[]colarr = new String[1];
			Map obj ;
			//如果是查询多个字段,把字段名分割放进数组
			if(colums.contains(",")){
				colarr = colums.split(",");
			}else{
				//只查一个字段
				colarr[0]=colums;
			}
			while(rs.next()){
				obj = new PageData();
				//便利这个字段名分割的数组
				for (String col : colarr) {
					obj.put(col.trim(), rs.getString(col.trim()));
				}
				list.add(obj);
			}
			return list;
		}
		/**
		 * 
		 * @Description:临时表创建
		 * @author:user
		 * @param colnum
		 * @return void
		 * @date 2017年5月3日
		 */
		public static void createTemp(int colnum) throws Exception{
			Connection con=getConnection();
			String sql = "";
			String sql1 = "IF EXISTS(Select 1 From Sysobjects Where Name='mytemp')  DROP table mytemp \n";
			sql+="CREATE TABLE mytemp ("+"\n";
			sql+="id int identity (1,1) primary key,"+"\n";
			for (int i = 0; i < colnum; i++) {
				sql+="td"+i+" varchar(255)DEFAULT NULL,"+"\n";
			}
			//sql+="td"+(num-1)+" varchar(255)DEFAULT NULL"+"\n"+")";
			sql=sql.substring(0,sql.lastIndexOf(","));
			sql+=");";
			System.out.println(sql);
			Statement stmt=con.createStatement();
			//先删除
			stmt.executeUpdate(sql1);
			stmt.executeUpdate(sql);
		}
		
		/**
		 * 
		 * @Description:临时表数据的插入
		 * @author:user
		 * @param 数据
		 * @param 表头列数
		 * @return void
		 * @date 2017年5月3日
		 */
		public static void insertTemp(List<TreeMap<String, Object>> list,int index) throws Exception {
			Connection con=getConnection();
			Statement stmt=con.createStatement();
			String sql ="INSERT INTO mytemp VALUES ";
			for (int k=1;k<list.size();k++) {
				 sql += "("+"\n";
				for (int i = 0; i <index; i++) {
					String str = (String) list.get(k).get("td"+i);
					if(str==null){
						str="";	
					}
					sql += "'"+str+"',";
				}
				sql=sql.substring(0, sql.lastIndexOf(","));
				sql += "),";
				//sqlserver中insert最多一次插入1000条，需要分批次插入
				if(k%900==0){
					sql=sql.substring(0, sql.lastIndexOf(","));
					System.out.println(sql);
					stmt.execute(sql);
					//清空sql字符串
					sql ="INSERT INTO mytemp VALUES ";
				}
			}
			sql=sql.substring(0, sql.lastIndexOf(","));
			System.out.println(sql);
			stmt.execute(sql);
		}
		
		public static void main(String[] args) throws Exception {
			System.out.println(getList("mytemp", "td1,td2", "",1,3));
		}
		
}
